1127. User Purchase Platform.sql

WITH each_day_platform AS(
SELECT spend_date,UNNEST(ARRAY['both','mobile','desktop']) AS platform_type
FROM spending_1127
GROUP BY spend_date
),
cte AS(
SELECTa.spend_date,
 CASE WHEN b.user_idIS NOT NULL THEN 'both'
 WHEN a.platform='mobile' THEN 'mobile'
 ELSE 'desktop'
 END AS platform_type,
COUNT(DISTINCT a.user_id) AS total_users,
SUM(a.amount) AS amount
FROM spending_1127 a
LEFT JOIN spending_1127 b ONa.user_id=b.user_idANDa.spend_date=b.spend_dateANDa.platform<>b.platform
GROUP BYa.spend_date,platform_type
)

SELECTa.spend_date,a.platform_type,COALESCE(total_users,0) AS total_users,COALESCE(amount,0) AS amount
FROM each_day_platform a
LEFT JOIN cte b ONa.spend_date=b.spend_dateANDa.platform_type=b.platform_type;